﻿using CuaHangLego.DataAccess;
using CuaHangLego.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace CuaHangLego.Bussiness
{
    public class HoaDonBSS
    {
        public IList<CTHD> ListCTHDBan;
        public IList<HoaDonBan> ListHDBan;
        private IList<SanPhamKho> ListSP;
        private IList<SanPhamThongKe> Cur;
        private int sum = 0;
        private int sum2 = 0;

        public HoaDonBSS()
        {
            this.Cur = new List<SanPhamThongKe>();
        }

        public void UpdateData()
        {
            this.ListCTHDBan = CTHDBanDA.Instance.GetAll();
            this.ListSP = SanPhamKhoDA.Instance.GetAll();
            this.ListHDBan = HoaDonBanDA.Instance.GetAll();
        }

        public int GetSum()
        {
            return this.sum;
        }

        public int GetSum2()
        {
            return this.sum2;
        }

        public IList<SanPhamThongKe> GetCurrentCTHDs()
        {
            return this.Cur;
        }

        public IList<SanPhamThongKe> GetAllSanPhamThongKe()
        {
            var data = from ct in this.ListCTHDBan
                       group ct by ct.SanPham.MaSo into g
                       select new { key = g.Key, sum = g.Sum(ct2 => ct2.SoLuong) };
            var datajoin = from sptk in data
                           join sp in this.ListSP on sptk.key equals sp.MaSo
                           select new SanPhamThongKe(sp.MaSo, sp, sptk.sum);
            this.sum = datajoin.Sum(d => d.SoLuongBan);
            this.sum2 = datajoin.Sum(d => (d.SanPham.GiaBan - d.SanPham.GiaMua) * d.SoLuongBan);
            this.Cur = datajoin.ToList();
            return this.Cur;
        }


        public IList<SanPhamThongKe> GetSanPhamThongKe(DateTime d1, DateTime d2, int sl1, int sl2)
        {
            var data1 = this.ListHDBan.Where(hd => (hd.DaThanhToan = true
                && DateTime.Parse(hd.ThoiGian.ToShortDateString()) >= DateTime.Parse(d1.ToShortDateString())
                && DateTime.Parse(hd.ThoiGian.ToShortDateString()) <= DateTime.Parse(d2.ToShortDateString())));

            var datajoin1 = from hd in data1
                            join ct in this.ListCTHDBan on hd.MaSo equals ct.MaHD
                            select ct;
            
            var data2 = from ct in datajoin1
                       group ct by ct.SanPham.MaSo into g
                       select new { key = g.Key, sum = g.Sum(ct2 => ct2.SoLuong) };

            var data3 = data2.Where(d => (d.sum >= sl1 && d.sum <= sl2));

            var datajoin2 = from sptk in data3
                           join sp in this.ListSP on sptk.key equals sp.MaSo
                           select new SanPhamThongKe(sp.MaSo, sp, sptk.sum);
            this.sum = datajoin2.Sum(d => d.SoLuongBan);
            this.Cur = datajoin2.ToList();
            return this.Cur;
        }

        public class SanPhamThongKe
        {
            public int MaSo { get; set; }
            public SanPhamKho SanPham { get; set; }
            public int SoLuongBan { get; set; }

            public int DoanhThu { get { return this.SanPham.GiaBan * this.SoLuongBan; } }

            public SanPhamThongKe(int MaSo, SanPhamKho SanPham, int SoLuongBan)
            {
                this.MaSo = MaSo;
                this.SanPham = SanPham;
                this.SoLuongBan = SoLuongBan;
            }
        }

        public void Export()
        {
            string dir = "C:";
            FolderBrowserDialog dlg = new System.Windows.Forms.FolderBrowserDialog();
            DialogResult result = dlg.ShowDialog();
            if (result == System.Windows.Forms.DialogResult.OK)
            {

                dir = dlg.SelectedPath;
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                Excel.Range chartRange;
                DateTime date = DateTime.Now;
                string link = "\\DoanhThu_"  + date.Year.ToString() + date.Month.ToString() + date.Day.ToString() + "_" + date.Hour.ToString() + date.Minute + date.Second + ".xls";

                xlApp = new Excel.Application();

                xlApp.StandardFont = "Arial";
                xlApp.StandardFontSize = 11;
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Name = "DOANH THU";

                xlWorkSheet.get_Range("A1", "E1").Merge(false);
                xlWorkSheet.get_Range("A1", "E1").Font.ColorIndex = 43;
                xlWorkSheet.get_Range("A1", "E1").Font.Size = 22;
                xlWorkSheet.Cells[1, 1] = "DOANH SỐ BÁN ĐƯỢC";

                xlWorkSheet.get_Range("A2", "E2").Font.Bold = true;
                xlWorkSheet.get_Range("A2", "E2").Interior.ColorIndex = 43;
                xlWorkSheet.get_Range("A2", "E2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.Cells[2, 1] = "STT";
                xlWorkSheet.Cells[2, 2] = "Mã sản phẩm";
                xlWorkSheet.Cells[2, 3] = "Tên sản phẩm";
                xlWorkSheet.Cells[2, 4] = "Số lượng";
                xlWorkSheet.Cells[2, 5] = "Doanh thu";

                for (int i = 0; i < this.Cur.Count; i++)
                {
                    xlWorkSheet.Cells[i + 3, 1] = i + 1;
                    xlWorkSheet.Cells[i + 3, 2] = Cur[i].MaSo;
                    xlWorkSheet.Cells[i + 3, 3] = Cur[i].SanPham.Ten;
                    xlWorkSheet.Cells[i + 3, 4] = Cur[i].SoLuongBan;
                    xlWorkSheet.Cells[i + 3, 5] = Cur[i].DoanhThu;
                }
                xlWorkSheet.Cells[this.Cur.Count + 3, 3] = "Tổng cộng";
                xlWorkSheet.Cells[this.Cur.Count + 3, 3].Font.Bold = true;
                xlWorkSheet.Cells[this.Cur.Count + 3, 4] = this.GetSum();
                xlWorkSheet.Cells[this.Cur.Count + 3, 4].Font.Bold = true;
                xlWorkSheet.Cells[this.Cur.Count + 3, 5] = this.GetSum2();
                xlWorkSheet.Cells[this.Cur.Count + 3, 5].Font.Bold = true;
                chartRange = xlWorkSheet.get_Range("A1", "Z200");
                chartRange.Columns.AutoFit();

                try
                {
                    xlWorkBook.SaveAs(dir + link, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                }
                catch
                {
                    MessageBox.Show("Không thể lưu file!");
                }
                finally
                {
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                }

                releaseObject(xlApp);
                releaseObject(xlWorkBook);
                releaseObject(xlWorkSheet);

                MessageBox.Show("Đã tạo report tại " + dir + link);
            }

            
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}
